package dao;

import classes.Agenda;
import classes.Servidor;
import conexao.GerenciadorConexao;
import controlador.LoginControlador;
import erro.ConexaoException;
import erro.ConsultaException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author R
 */
public class AgendaDao {

    private GerenciadorConexao g;

    public AgendaDao() {
        g = GerenciadorConexao.getInstancia();
    }

    /**
     * 
     * @param a = Agenda
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void inserirAgenda(Agenda a) throws ConexaoException, ConsultaException {
        String sql = "INSERT INTO agenda (assunto,local,data,horaI,obs,logado,status) VALUES (?,?,?,?,?,?,?)";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            Date data = a.getData();
            a.setData(data);
            Date horaI = a.getHoraI();
            a.setHoraI(horaI);
            pstm.setString(1, a.getAssunto());
            pstm.setString(2, a.getLocal());
            pstm.setDate(3, new java.sql.Date(data.getTime()));
            pstm.setTime(4, new java.sql.Time(horaI.getTime()));
            pstm.setString(5, a.getObs());
            pstm.setInt(6, a.getLogado().getMatricula());
            pstm.setInt(7, a.getStatus());
            pstm.executeUpdate();

        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public ArrayList<Agenda> listar() throws ConexaoException, ConsultaException {
        ArrayList<Agenda> lista = new ArrayList<Agenda>();
        Agenda a;
        String sql = "SELECT agenda_id,assunto,local,data,obs,horaI,logado,status FROM agenda order by data asc";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                a = new Agenda();
                a.setAgenda_id(rs.getInt("agenda_id"));
                a.setAssunto(rs.getString("assunto"));
                a.setLocal(rs.getString("local"));
                a.setData(rs.getDate("data"));
                a.setObs(rs.getString("obs"));
                a.setHoraI(rs.getTime("horaI"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("logado"));
                a.setLogado(s);
                a.setStatus(rs.getInt("status"));
                lista.add(a);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public ArrayList<Agenda> listarFinalizados() throws ConexaoException, ConsultaException {
        ArrayList<Agenda> lista = new ArrayList<Agenda>();
        Agenda a;
        String sql = "SELECT agenda_id,assunto,local,data,obs,horaI,logado,status FROM agenda WHERE status=2 order by data asc";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                a = new Agenda();
                a.setAgenda_id(rs.getInt("agenda_id"));
                a.setAssunto(rs.getString("assunto"));
                a.setLocal(rs.getString("local"));
                a.setData(rs.getDate("data"));
                a.setObs(rs.getString("obs"));
                a.setHoraI(rs.getTime("horaI"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("logado"));
                a.setLogado(s);
                a.setStatus(rs.getInt("status"));
                lista.add(a);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public ArrayList<Agenda> listarLogado() throws ConexaoException, ConsultaException {
        ArrayList<Agenda> lista = new ArrayList<Agenda>();
        Agenda a = new Agenda();
        String lc = LoginControlador.login;
        Servidor s = new Servidor();
        ServidorDao sdao = new ServidorDao();
        s.setLogin(lc);
        sdao.pesquisarLogin(s);
        a.setLogado(s);
        String sql = "SELECT agenda_id,assunto,local,data,obs,horaI,logado,status FROM agenda WHERE logado=?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, a.getLogado().getMatricula());
            ResultSet rs = pstm.executeQuery();

            while (rs.next()) {
                a = new Agenda();
                a.setAgenda_id(rs.getInt("agenda_id"));
                a.setAssunto(rs.getString("assunto"));
                a.setLocal(rs.getString("local"));
                a.setData(rs.getDate("data"));
                a.setObs(rs.getString("obs"));
                a.setHoraI(rs.getTime("horaI"));
                s.setMatricula(rs.getInt("logado"));
                a.setLogado(s);
                a.setStatus(rs.getInt("status"));
                lista.add(a);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public ArrayList<Agenda> listarAbertas() throws ConexaoException, ConsultaException {
        ArrayList<Agenda> lista = new ArrayList<Agenda>();
        Agenda a;
        String sql = "SELECT agenda_id,assunto,local,data,obs,horaI,logado,status FROM agenda WHERE status=1 order by data asc";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);

            while (rs.next()) {
                a = new Agenda();
                a.setAgenda_id(rs.getInt("agenda_id"));
                a.setAssunto(rs.getString("assunto"));
                a.setLocal(rs.getString("local"));
                a.setData(rs.getDate("data"));
                a.setObs(rs.getString("obs"));
                a.setHoraI(rs.getTime("horaI"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("logado"));
                a.setLogado(s);
                a.setStatus(rs.getInt("status"));
                lista.add(a);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @param a = Agenda
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public Agenda pesquisar(Agenda a) throws ConexaoException, ConsultaException {
        Servidor s = new Servidor();
        String sql = "SELECT * FROM agenda where agenda_id=?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, a.getAgenda_id());
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                a.setAgenda_id(rs.getInt("agenda_id"));
                a.setAssunto(rs.getString("assunto"));
                a.setObs(rs.getString("obs"));
                a.setData(rs.getDate("data"));
                a.setHoraI(rs.getTime("horaI"));
                a.setLocal(rs.getString("local"));
                s.setMatricula(rs.getInt("logado"));
                a.setLogado(s);
                a.setStatus(rs.getInt("status"));
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return a;
    }

    /**
     * 
     * @param a = Agenda
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void deletar(Agenda a) throws ConexaoException, ConsultaException {

        String sql = "DELETE FROM agenda where agenda_id =?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, a.getAgenda_id());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @param a = Agenda
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void alterar(Agenda a) throws ConexaoException, ConsultaException {

        String sql = "UPDATE agenda SET assunto=?,local=?,obs=?,data=?,horaI=?,status=? WHERE agenda_id=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setString(1, a.getAssunto());
            pstm.setString(2, a.getLocal());
            pstm.setString(3, a.getObs());
            pstm.setDate(4, new java.sql.Date(a.getData().getTime()));
            pstm.setTime(5, new java.sql.Time(a.getHoraI().getTime()));
            pstm.setInt(6, a.getStatus());

            pstm.setInt(7, a.getAgenda_id());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }
}
